<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ page contentType="text/html; charset=UTF-8" import="demo.*"%>
<%@ page import="java.sql.*"%>

<HEAD>
	<TITLE>Registration</TITLE>
	<META http-equiv=Content-Type content="text/html; charset=gb2312">

</HEAD>

<BODY leftMargin=0 topMargin=10>

	<TABLE cellSpacing=0 cellPadding=0 width=700 align=center border=0>
		<TBODY>
			<TR>
				<TD>
					<DIV align=center>
						<A href="index.jsp"><IMG height=118
								src="Pics/restaurantLogo.JPG" width=242 border=0>
						</A>
					</DIV>
				</TD>
			</TR>
		</TBODY>
	</TABLE>

	<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
		<TR>
			<TD vAlign=top height=60>
				<FORM name="delivery_status" action="administrator_account.jsp"
					method=post>

					<TABLE cellSpacing=1 cellPadding=3 width="60%" align=center
						bgColor=#cccccc border=0>
						<TBODY>

							<tr bgColor=#bbbbbb>
								<td align="center" colspan=3>
									Update Delivery State
								</td>
							</tr>
							<tr>
								<td>
									Types of Order Form
								</td>
								<td>
									Customer ID
								</td>
								<td>
									Customer Name
								</td>
							<TR>
								<td>
									<select name="select_status" style="width: 140px">
										<option value="any">
											any
										</option>
										<option value="not cooked yet">
											not cooked yet
										</option>
										<option value="cooking">
											cooking
										</option>
										<option value="ready to deliver">
											ready to deliver
										</option>
										<option value="delivering">
											delivering
										</option>
										<option value="done">
											done
										</option>
									</select>
								</td>
								<td>
									<input name="cus_ID_search" type="text" class="input" size="20">
								</td>
								<td>
									<input name="cus_name_search" type="text" class="input"
										size="20">
								</td>
							</TR>
							<TR>
								<TD>
									<INPUT type=submit value=search name=SubmitSearchOrderForm>
								</TD>
							</TR>
						</TBODY>
					</TABLE>
				</FORM>

			</TD>
		</TR>

		<tr>
			<td colspan=3>
				<form action="administrator_account.jsp" method=post>
					<TABLE cellSpacing=1 cellPadding=3 width="60%" align=center
						bgColor=#eeeeee>
						<%
							String OrderFormSearchButtonPressed = request
									.getParameter("SubmitSearchOrderForm");
							if (OrderFormSearchButtonPressed != null
									&& OrderFormSearchButtonPressed.length() > 0) {
								out
										.println("<tr bgColor=#cccccc><td>order form ID</td><td>customer name</td><td>order time</td><td>order status</td><td>update status</td></tr>");

								DBConnector db = DBConnector.getDBConnector();
								String OrderStatus = request.getParameter("select_status");
								String CusID = request.getParameter("cus_ID_search");
								String CusName = request.getParameter("cus_name_search");

								if (OrderStatus != null) {
									String DeliveryState[] = {
											"<option value='not cooked yet'>not cooked yet</option>",
											"<option value='cooking'>cooking</option>",
											"<option value='ready to deliver'>ready to deliver</option>",
											"<option value='delivering'>delivering</option>",
											"<option value='done'>done</option>" };
									String statement = "select form_ID, cus_name, form_time, form_state from order_form, customer where customer.cus_ID = order_form.cus_ID ";
									if (!OrderStatus.equals("any"))
										statement += "and form_state = '" + OrderStatus + "' ";
									if (CusID.length() > 0)
										statement += "and customer.cus_ID = " + CusID + " ";
									if (CusName.length() > 0)
										statement += "and cus_name like '%" + CusName + "%' ";
									statement += "order by form_time desc";

									db.prepareStatement(statement);
									ResultSet OrderInfo = db.queryPstmt();

									if (OrderInfo != null) {
										while (OrderInfo.next()) {
											out.println("<tr>");
											out.println("<td>" + OrderInfo.getString(1)
													+ "</td>");
											out.println("<td>" + OrderInfo.getString(2)
													+ "</td>");
											out.println("<td>" + OrderInfo.getString(3)
													+ "</td>");
											out.println("<td>" + OrderInfo.getString(4)
													+ "</td>");

											String status = OrderInfo.getString(4);

											int index = 0;
											if (status.equals("done"))
												out
														.println("<td>You cannot change the status.</td>");
											else {
												if (status.equals("not cooked yet")) {
													index = 0;
												} else if (status.equals("cooking")) {
													index = 1;
												} else if (status.equals("ready to deliver")) {
													index = 2;
												} else if (status.equals("delivering")) {
													index = 3;
												}

												String OptionItems = "";
												for (int i = index; i < 5; i++)
													OptionItems = OptionItems
															+ DeliveryState[i];

												out.println("<td><select name='"
														+ OrderInfo.getString(1)
														+ "' style='width:140px'>"
														+ OptionItems + "</select></td>");
											}
											out.println("</tr>");

										}
										out
												.println("<tr><td colspan = 5><INPUT type=submit value=submit name=SubmitUpdate></td></tr>");
									} else
										out
												.println("<tr><td colspan = 5>There are no order forms fulfilling yoru requirement.</td></tr>");
								}
							}
						%>

					</table>
				</form>
			</td>
		</tr>
		<tr>
			<td>
				<form action="administrator_account.jsp">
					<TABLE cellSpacing=1 cellPadding=3 width="60%" align=center
						bgColor=#cccccc>
						<tr bgColor=#bbbbbb>
							<td align=center colspan=6>
								The Food Sale
							</td>
						</tr>
						<tr>
							<td>
								Keyword :
								<input name="food_search" type="text" class="input" size="20">
							</td>
							<td>
								Order By
							</td>
							<td>
								<input name='Order_By_Restaurant' type='radio' class='input'
									value='Order_By_Restaurant'>
								Restaurant Name
							</td>
							<td>
								<input name='Order_By_Price' type='radio' class='input'
									value='Order_By_Price'>
								Price
							</td>
							<td>
								<input name='Order_By_Sales' type='radio' class='input'
									value='Order_By_Sales'>
								Sales
							</td>
							<td>
								<input name='Order_By_FoodName' type='radio' class='input'
									value='Order_By_FoodName'>
								Food Name
							</td>
						</tr>
						<tr>
							<td>
								<INPUT type=submit value=search name=SubmitFoodSearch>
							</td>
						</tr>
					</TABLE>
				</form>
				<TABLE cellSpacing=1 cellPadding=3 width="60%" align=center
					bgColor=#eeeeee>
					<%
						//DBConnector db = DBConnector.getDBConnector();	
						String SubmitFoodSearch = request.getParameter("SubmitFoodSearch");
						if (SubmitFoodSearch != null && SubmitFoodSearch.length() > 0) {
							String food_search = request.getParameter("food_search");
							String Order_By_FoodName = request
									.getParameter("Order_By_FoodName");
							String Order_By_Restaurant = request
									.getParameter("Order_By_Restaurant");
							String Order_By_Price = request.getParameter("Order_By_Price");
							String Order_By_Sales = request.getParameter("Order_By_Sales");
							DBConnector db = DBConnector.getDBConnector();
							String queryFoods = "select food_name, rest_name, food_price, food_quantity from food, restaurant where food.rest_ID = restaurant.rest_ID ";

							if (food_search != null && food_search.length() > 0)
								queryFoods += "and  (food_name like '%" + food_search
										+ "%' or rest_name like '%" + food_search
										+ "%' or food_price like '%" + food_search + "%') ";
							if (Order_By_Restaurant != null && Order_By_Restaurant.length() > 0)
								queryFoods += "order by restaurant.rest_name ";
							else if (Order_By_Price != null && Order_By_Price.length() > 0)
								queryFoods += "order by food_price ";
							else if (Order_By_Sales != null && Order_By_Sales.length() > 0)
								queryFoods += "order by food_quantity desc";
							else if (Order_By_FoodName != null && Order_By_FoodName.length() > 0)
								queryFoods += "order by food_name ";

							db.prepareStatement(queryFoods);
							ResultSet queryFoodResult = db.queryPstmt();
							if (queryFoodResult != null) {
								out.println("<tr bgColor=#cccccc><td>Food Name</td><td>Restaurant Name</td><td>Food Price</td><td># Sale</td></tr>");
								if (queryFoodResult.next()) {
									do {
										out.println("<tr>");
										out.println("<td>" + queryFoodResult.getString(1)
												+ "</td>");
										out.println("<td>" + queryFoodResult.getString(2)
												+ "</td>");
										out.println("<td>" + queryFoodResult.getString(3)
												+ "</td>");
										out.println("<td>" + queryFoodResult.getString(4)
												+ "</td>");
										out.println("</tr>");
									} while (queryFoodResult.next());
								} else
									out
											.println("<tr><td colspan = 5>There are no food information fulfilling your requirement.</td></tr>");
							}
						}
					%>
				</TABLE>
			</td>
		</tr>
		<tr><td><br>
		
		<form action="administrator_account.jsp">
					<TABLE cellSpacing=1 cellPadding=3 width="60%" align=center
						bgColor=#cccccc>
						<tr bgColor=#bbbbbb>
							<td align=center colspan=6>
								The Messages
							</td>
						</tr>
						<tr>
							<td>
								Keyword :
								<input name="message_search" type="text" class="input" size="20">
							</td>
							<td>
								Order By
							</td>							
							<td>
								<input name='Order_By_Topic' type='radio' class='input'
									value='Order_By_SenderName'>
								Sender Name
							</td>
							<td>
								<input name='Order_By_SenderName' type='radio' class='input'
									value='Order_By_Topic'>
								Topic
							</td>
							<td>
								<input name='Order_By_Email' type='radio' class='input'
									value='Order_By_Email'>
								Email
							</td>
						</tr>
						<tr>
							<td>
								<INPUT type=submit value=search name=SubmitMessageSearch>
							</td>
						</tr>						
					</TABLE>
				</form>	
				<TABLE width=60% align = center border=0 cellspacing = 3><tr><td  bgcolor="#cccccc">			
		<%		try{		
						DBConnector db = DBConnector.getDBConnector();	
						String SubmitMessageSearch = request.getParameter("SubmitMessageSearch");
						if (SubmitMessageSearch != null && SubmitMessageSearch.length() > 0) {
							db.prepareStatement("select count(*) from message");
							ResultSet result=db.queryPstmt(); 
							int message_count=0; 
							if(result.next()) 
							{ 
								message_count=result.getInt(1); 
								result.close(); 
							} 
							if(message_count>0) 
							{ 
						
								String message_search = request.getParameter("message_search");		
								String Order_By_SenderName = request.getParameter("Order_By_SenderName");
								String Order_By_Topic = request.getParameter("Order_By_Topic");
								String Order_By_Email = request.getParameter("Order_By_Email");
								
								String queryMessages = "select * from message ";
	
								if (message_search != null && message_search.length() > 0)
									queryMessages += "where  (message_title like '%" + message_search
											+ "%' or message_name like '%" + message_search
											+ "%' or message_content like '%" + message_search
											+ "%' or message_mail like '%" + message_search + "%') ";	
								queryMessages += "order by null ";					
								if (Order_By_SenderName != null && Order_By_SenderName.length() > 0)
									queryMessages += ", message_name ";
								else if (Order_By_Topic != null && Order_By_Topic.length() > 0)
									queryMessages += ", message_title";
								else if (Order_By_Email != null && Order_By_Email.length() > 0)
									queryMessages += ", message_email ";
								else 
									queryMessages += ", message_time desc ";
								out.println(queryMessages);
								
								db.prepareStatement(queryMessages);
								result = db.queryPstmt();
								while(result.next()) 
								{ 
									String title=result.getString("message_title"); 
									String name=result.getString("message_name"); 
									String mail=result.getString("message_mail"); 
									Timestamp time=result.getTimestamp("message_time"); 
									Date date_time=new Date(time.getTime()); 
									Time time_time=new Time(time.getTime()); 
									String content=result.getString("message_content"); 
							
									%>			
		
		<TABLE width="100%" align="center" border=0 cellspacing="3" cellpadding="3" > 
		<tr>
		<td><font size=2>Sender:</font></td>
		<td><font size=2>Topic:</font></td> 
		<td><font size=2>E-mail:</font></td> 
		<td><font size=2>Time:</font></td>
		</tr> 
		<tr bgcolor="#eeeeee"><td><%=name%></td><td><%=title%></td>
		<td> 
									<% 
									out.println("<a href=mailto:"+mail+">"+mail+"</a>"); 
									%> 
		</td>
		<td> 
									<% 
									out.println("<font size=2>"+date_time+" "+time_time+"</font>"); 
									%> 
		</td></tr>
		<tr><td colspan = 2><font size=2>Message:</font></td>
		<td align="right" colspan = 2> 
									<% 
									out.println("("+message_count+")"); 
									%> 
		</td></tr>
		<tr bgcolor="#eeeeee">
		 <td colspan=4>
		 <table width = 80% align = center><tr><td><%=content%></td></tr></table>
		</td></tr>
		<tr><td colspan = 4 align = center>
									<%
									out.println("<form action='administrator_account.jsp'>");
									out.println(" <INPUT type=checkbox value=" + result.getString("message_ID") + " name=DeleteMessage>delete ");
									out.println(" <INPUT type=submit value=apply name=SubmitDeleteMessage>");
									out.println("</form>");
									 %>		
		</td></tr>
		
									<% 									
									message_count--; 
								} 
							}
							else 
							{ 
								out.println("<script>alert(unescape('There are no messages.'))</script>");
							
							} 
						
						result.close(); 
						//con.close(); 
						} 
						
				} catch(Exception e) 
				{out.println(e);} 
				%> 
			</table> </td></tr>
			</table>
			
			<%
				String SubmitMessageSearch = request.getParameter("SubmitDeleteMessage");
				if (SubmitMessageSearch != null && SubmitMessageSearch.length() > 0)
				{
					String DeleteMessageID = request.getParameter("DeleteMessage");
					String DeleteMessageStatement = "delete from message where message_ID = " + DeleteMessageID;
					DBConnector db = DBConnector.getDBConnector();	
					db.prepareStatement(DeleteMessageStatement);
					db.updatePstmt();
					out.println("<script>alert(unescape('The message has been deleted.'))</script>");
				}
				
			 %>
			 
			 <%
			 
			 String SubmitUpdate = request.getParameter("SubmitUpdate");
			 if (SubmitUpdate != null && SubmitUpdate.length() > 0)
			 {
				DBConnector db = DBConnector.getDBConnector();		
				//String Rest_ID = RestaurantQuery.getRestaurantSelected();
				
				db.prepareStatement("select max(form_ID) from order_form");
				ResultSet NumOrderForm = db.queryPstmt();
				NumOrderForm.next();
				int iNumOrderForm = NumOrderForm.getInt(1);
				String sNumOrderForm = NumOrderForm.getString(1);
				int i = 1;
				while (i <= iNumOrderForm)
				{
					String index = "" + i;
					String OrderStatus = request.getParameter(index);
					if (OrderStatus != null)
					{
						String statement = "update order_form set form_state = '" + OrderStatus + "' where form_ID = " + index;
						db.prepareStatement(statement);
						db.updatePstmt();
						
						//if the order_form state is "done" which means the whole order process is complete, 
						//then the food sale quantity will be updated. The restaurant revenue will also be updated
						//the cost of the restaurnant will also be updated
						if (OrderStatus.equals("done"))
						{
							String queryFoods = "select food.food_ID, order_quantity, food_price, food_cost from contain, food where food.food_ID = contain.food_ID and form_ID = " + index;
							db.prepareStatement(queryFoods);
							ResultSet Foods = db.queryPstmt();
							if (Foods != null)
							{
								while (Foods.next())
								{
									String updateFoods = "update food set food_quantity = food_quantity + " + Foods.getString(2) + " where food_ID = " + Foods.getString(1);
									db.prepareStatement(updateFoods);
									db.updatePstmt();
									
									String queryRestaurants = "select restaurant.rest_ID, rest_revenue, rest_cost from restaurant, food where food.rest_ID = restaurant.rest_ID and food.food_ID = " + Foods.getString(1); 
									db.prepareStatement(queryRestaurants);
									ResultSet Restaurants = db.queryPstmt();
									
									if (Restaurants != null)
									{
										Restaurants.next();
										float revenue = Foods.getInt(2) * Foods.getFloat(3) + Restaurants.getFloat(2);
										float cost = Foods.getInt(2) * Foods.getFloat(4) + Restaurants.getFloat(3);								
										String updateRestaurantRevenue = "update restaurant set rest_revenue = " + Float.toString(revenue) + " where rest_ID = " + Restaurants.getString(1);
										db.prepareStatement(updateRestaurantRevenue);
										db.updatePstmt();
										String updateRestaurantCost = "update restaurant set rest_cost = " + Float.toString(cost) + " where rest_ID = " + Restaurants.getString(1);
										db.prepareStatement(updateRestaurantCost);
										db.updatePstmt();
									}													
								}
							}					
						}
					}
					i++;
				}	
				out.println("<script>alert(unescape('The status update is successful.'))</script>");
			}
		%>   
	</TABLE>
</BODY>
